import os
import re
from datetime import datetime

import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

def now():
    return datetime.now()
def clear():
    folder_path = os.listdir('E:/spider_download')
    for file in folder_path:
        data_path0 = 'E:/spider_download' + '/' + file
        os.remove(data_path0)
    print("---clear_folder_success")
def convert_time(time_str):
    time_str = str(time_str)
    pattern = r'(\d+)小时(\d+)分'
    match = re.match(pattern, time_str)
    if match:
        hours = int(match.group(1))
        minutes = int(match.group(2))
        return round(hours + minutes / 60, 2)
    else:
        return None
def first_letter(filename):
    match = re.search('[a-zA-Z]', str(filename))
    if match:
        first_letter = match.group()
        return first_letter
def append_to_excel(df, file_path, sheet_name):
    # 创建 ExcelWriter 对象
    with pd.ExcelWriter(file_path, mode='a') as writer:
        # 将 DataFrame 写入 Excel 文件的新 sheet 中
        df.to_excel(writer, sheet_name=sheet_name, index=False)
def auto_columns_size(file_path):
    wb = load_workbook(filename=file_path)
    for i in range(len(wb.sheetnames)):
        ws = wb[wb.sheetnames[i]]
        # 设置列宽
        # 设置一个字典用于保存列宽数据
        dims = {}
        # 遍历表格数据，获取自适应列宽数据
        for row in ws.rows:
            for cell in row:
                if cell.value:
                    # 遍历整个表格，把该列所有的单元格文本进行长度对比，找出最长的单元格
                    # 在对比单元格文本时需要将中文字符识别为1.7个长度，英文字符识别为1个，这里只需要将文本长度直接加上中文字符数量即可
                    # re.findall('([\u4e00-\u9fa5])', cell.value)能够识别大部分中文字符
                    cell_len = 0.7 * len(re.findall('([\u4e00-\u9fa5])', str(cell.value))) + len(str(cell.value))
                    dims[cell.column] = max((dims.get(cell.column, 0), cell_len))
        for col, value in dims.items():
            # 设置列宽，get_column_letter用于获取数字列号对应的字母列号，最后值+2是用来调整最终效果的
            ws.column_dimensions[get_column_letter(col)].width = value + 4
        # 保存工作簿（覆盖原文件）
    wb.save(file_path)